
use "$root/data/interim/adminassistants_bg.dta", replace

keep if substr(soc,1,2)=="43"

gen numads=1

replace soc="43-5030" if soc=="43-5031"
replace soc="43-5030" if soc=="43-5032"
replace soc="43-5111" if soc=="43-511."

replace soc="43-6010" if soc=="43-6011"
replace soc="43-6010" if soc=="43-6012"
replace soc="43-6010" if soc=="43-6013"
replace soc="43-6010" if soc=="43-6014"

collapse (sum) m_excel m_office any_tech count_tech numads, by(soc year czone)
rename m_excel excel
rename m_office office

fillin soc year czone
drop if soc==""|czone==.|year==.
foreach var in any_tech count_tech numads excel office{
	replace `var'=0 if `var'==.
}
gen mean_any_tech= any_tech/numads
gen mean_count_tech=count_tech/numads
gen mean_excel= excel/numads
gen mean_office=office/numads

egen sum_any_tech=sum(any_tech), by (year soc)
egen sum_count_tech=sum(count_tech), by (year soc)
egen sum_excel=sum(excel), by (year soc)
egen sum_office=sum(office), by (year soc)

egen sum_numads=sum(numads), by (year soc)
gen mean_count_tech_oth_czs=(sum_count_tech-count_tech)/(sum_numads-numads)
gen mean_any_tech_oth_czs=(sum_any_tech-any_tech)/(sum_numads-numads)
gen mean_excel_oth_czs=(sum_excel-excel)/(sum_numads-numads)
gen mean_office_oth_czs=(sum_office-office)/(sum_numads-numads)

keep soc year czone numads mean_any_tech mean_any_tech_oth_czs mean_count_tech mean_count_tech_oth_czs  mean_office mean_office_oth_czs mean_excel mean_excel_oth_czs
order soc year czone numads mean_any_tech mean_any_tech_oth_czs mean_count_tech mean_count_tech_oth_czs  mean_office mean_office_oth_czs mean_excel mean_excel_oth_czs
gen soccode =soc
merge m:1 soccode using  "$root/data/data_public/soc_to_census2010.dta"
keep if _merge==3
drop _merge
drop soccode
rename censuscode census2010
rename occ_soc soc2010
compress
save "$root/data/interim/mean_tech_by_cz_census_2016_collapsed.dta",replace

